﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using IrisGenerator.Common.DatabaseModel;

namespace IrisGenerator.Modules.SqlServer2005.DataAccess
{
    internal class SystemProcedures
    {
        internal static SqlDataReader GetTablesFromModel(string connectionString)
        {
            string sql = "sp_tables";

            SqlConnection conn = new SqlConnection(connectionString);

            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 180;
            SqlDataReader dr = null;
            
            try
            {
                conn.Open();
                dr = cmd.ExecuteReader();

                return dr;
            }
            catch (SqlException sqlex)
            {
                if (dr != null)
                    dr.Dispose();
                if (cmd != null)
                    cmd.Dispose();
                if (conn != null)
                    conn.Dispose();
                throw new Exception("Exception SQL", sqlex);
            }
            catch (Exception ex)
            {
                if (dr != null)
                    dr.Dispose();
                if (cmd != null)
                    cmd.Dispose();
                if (conn != null)
                    conn.Dispose();
                throw new Exception("Exception", ex);
            }
            
            return null;
        
        }

        internal static SqlDataReader GetColumnsFromModel(string tableName,string schema,string connectionString)
        {
            string procedure = "sp_MShelpcolumns";
            SqlConnection conn = new SqlConnection(connectionString);

            SqlCommand cmd = new SqlCommand(procedure, conn);
            cmd.CommandTimeout = 180;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            if (string.IsNullOrEmpty(schema))
                cmd.Parameters.Add(new SqlParameter("@tablename", tableName));
            else
                cmd.Parameters.Add(new SqlParameter("@tablename", schema + "." + tableName));

            SqlDataReader dr = null;
            ColumnCollection col = null;

            try
            {
                conn.Open();
                dr = cmd.ExecuteReader();
                return dr;
            }
            catch (SqlException sqlex)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
            
        }

        internal static SqlDataReader GetPrimaryKeyFromTable(string tableName, string schema, string connectionString)
        {
            string requete = "sp_primary_keys_rowset";
            SqlConnection conn = new SqlConnection(connectionString);

            SqlCommand cmd = new SqlCommand(requete, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandTimeout = 180;

            cmd.Parameters.Add(new SqlParameter("@table_name", tableName));

            SqlDataReader dr = null;
            
            try
            {
                conn.Open();
                dr = cmd.ExecuteReader();
                return dr;
            }
            catch (SqlException sqlex)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
            
        }

        internal static SqlDataReader GetForeignKeysFromTable(string tableName, string schema, string connectionString)
        {
            string requete = "sp_foreign_keys_rowset";

            SqlConnection conn = new SqlConnection(connectionString);

            SqlCommand cmd = new SqlCommand(requete, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandTimeout = 180;

            cmd.Parameters.Add(new SqlParameter("@pk_table_name", tableName));
            SqlDataReader dr = null;
            try
            {
                conn.Open();
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch
            {
                throw;
            }

            
        }
    }
}
